<?php
/**
 * WpConnectSqlite3.php
 * 
 * This file contains the WpConnectSqlite3 class which provides database access functions for sqlite3
 * @author Robert Schmidt <robert@wildpack.de>
 * @version 1.0
 * @package wildpack
 * @subpackage dataaccess.wpconnect
 */

/**
 * The IWpConnect Interface to implement
 */
require_once(dirname(__FILE__).'/IWpConnect.php');

/**
 * The WpConnectBase class to extend
 */
require_once(dirname(__FILE__).'/WpConnectSql.php');

/**
 * WpConnectSqlite3
 * 
 * The WpConnectSqlite3 class provides database access functions for sqlite3
 * 
 * Example:
 * <code>
 * $conn = new WpConnectSqlite3(array(
 *    'location' => 'localhost/a_database',
 *    'user' => 'a_user',
 *    'password' => 'a_password')
 * );
 * 
 * //-- Select data --
 * $conn->select('*', 'table', 'WHERE id = 1);
 * $arr = $conn->getResult('assocs');
 * foreach ($arr as $index => $dataset) {
 *   foreach ($dataset as $field => $value) {
 *     print('Dataset: '.strval($index).', Field: '.$field.', Value: '.strval($value));
 *   }
 * }
 * 
 * //-- Insert data --
 * $chk = $conn->insert('table', array(
 *    'id' => 2,
 *    'field1' => 'fieldvalue', //A varchar field
 *    'field2' => 2, //An int field
 *    'field3' => 1.5 //A float field
 * ));
 * 
 * if ($chk === true) {
 *   print('Successfully inserted.');
 * }
 * </code>
 * 
 * @author Robert Schmidt <robert@wildpack.de>
 * @version 1.0
 * @package wildpack
 * @subpackage dataaccess.wpconnect
 */
class WpConnectSqlite3 extends WpConnectSql implements IWpConnect
{
  /**
   * The last used connection resource, is needed to keep access on the result
   */
	private $lastConnection;
	
	/**
   * The id of the last inserted dataset
   */
	private $varInsertId = null;
	
	/**
   * The template for the limit statement {start} and {limit} as placeholders
   */
	protected $LimitTemplate = 'LIMIT {start}, {limit}'; //Override the limit template for Sqlite
	
	/**
	 * Restrieves a list of fields an necessary information
	 * @param object $rawresult The raw result from datasource
	 * @return An associative array with field names as keys containing an assoc array with the following information
	 * table (string), null (bool), pk (bool), unique (bool), index (bool), numeric (bool), type (string)
	 */
	private function getFieldInfo($rawresult, $asAssoc = true)
	{
		$arrReturn = array();
		
		for($i=0; $i<$rawresult->numColumns(); $i++) {
			$row = array(
				'name' => $rawresult->columnName($i),
				'type' => $rawresult->columnType($i)
			);
			
			if ($asAssoc === true) {
				$arrReturn[$rawresult->columnName($i)] = $row;
			} else {
				$arrReturn[] = $row;
			}
		}
		return $arrReturn;
	}
	
	/**
	 * Converts the specific data type into a basic data type
	 * @param string $dataType The data type to convert or false
	 * @param mixed $value The value to convert
	 * @return The converted data type
	 */
	private function convertType($dataType, $value)
	{
		if (is_null($value)) {
			return null;
		}
		
		$arrFloat = array('float', 'double', 'real');
		$arrInt = array('int', 'long', 'integer', 'mediumint', 'smallint', 'bigint', 'tinyint');
		
		if (strpos($dataType, '(') !== false) {
			$dataType = substr($dataType, 0, strpos($dataType, '('));
		}
		
		if (in_array($dataType, $arrFloat, true) === true) {
			return floatval($value);
		}
		
		if (in_array($dataType, $arrInt, true) === true) {
			return intval($value);
		}
		return $value;
	}
	
	/**
   * Convert the rawresult to an array of objects (stdClass)
   * @return An array the selected data
   */
	protected function convertToObjects()
	{
		$arrReturn = array();
		$arrFields = array();
		$rawresult = $this->RawResult;
		$varTemp = null;
		
		//-- Check parameter --
		if (isset($rawresult) === false || is_null($rawresult) === true) {
			return null;
		}
		if (get_class($rawresult) !== 'SQLite3Result') {
			return $rawresult;
		}
		
		$arrFields = $this->getFieldInfo($rawresult, true);
		
		$rawresult->reset();
		while ($row = $rawresult->fetchArray(SQLITE3_ASSOC)) {
			unset($varTemp);
			
			foreach ($row as $strField => $varValue) {
				$varTemp->$strField = $this->convertType($arrFields[$strField]['type'] ,$varValue);
			}
			$arrReturn[] = $varTemp;
		}
		
		return $arrReturn;
	}
	
	/**
   * Convert the rawresult to an array of arrays
   * @return An array the selected data
   */
	protected function convertToArrays()
	{
		$arrReturn = array();
		$arrFields = array();
		$rawresult = $this->RawResult;
		$varTemp = null;
		
		//-- Check parameter --
		if (isset($rawresult) === false || is_null($rawresult) === true) {
			return null;
		}
		if (get_class($rawresult) !== 'SQLite3Result') {
			return $rawresult;
		}
		
		$rawresult->reset();
		$arrFields = $this->getFieldInfo($rawresult, true);
		
		$rawresult->reset();
		while ($row = $rawresult->fetchArray(SQLITE3_ASSOC)) {
			unset($varTemp);
			$varTemp = array();
			$intFdC = 0;
			
			foreach ($row as $strField => $varValue) {
				$varTemp[$intFdC] = $this->convertType($arrFields[$intFdC]['type'] ,$varValue);
				$intFdC++;
			}
			$arrReturn[] = $varTemp;
		}
		
		return $arrReturn;
	}
	
	/**
   * Returns an array of associative arrays (fieldnames as array keys) of the retrieved data 
   * @return An array the selected data
   */
	protected function convertToAssocs()
	{
		$arrReturn = array();
		$arrFields = array();
		$rawresult = $this->RawResult;
		$varTemp = null;
		
		//-- Check parameter --
		if (isset($rawresult) === false || is_null($rawresult) === true) {
			return null;
		}
		if (get_class($rawresult) !== 'SQLite3Result') {
			return $rawresult;
		}
		
		$arrFields = $this->getFieldInfo($rawresult, true);
		
		$rawresult->reset();
		while ($row = $rawresult->fetchArray(SQLITE3_ASSOC)) {
			unset($varTemp);
			
			foreach ($row as $strField => $varValue) {
				$varTemp[$strField] = $this->convertType($arrFields[$strField]['type'] ,$varValue);
			}
			$arrReturn[] = $varTemp;
		}
		
		return $arrReturn;
	}

	/**
   * Converts the raw data using a custom algorithm
   * @param object $custom A custom class instance to use for converting
   */
	protected function convertToCustom(&$custom)
	{
		$arrReturn = array();
    $arrFields = array();
    $rawresult = $this->RawResult;
    $varTemp = null;
    
    //-- Check parameter --
    if (isset($rawresult) === false || is_null($rawresult) === true) {
      return null;
    }
    if (get_class($rawresult) !== 'SQLite3Result') {
      return $rawresult;
    }
    
    $arrFields = $this->getFieldInfo($rawresult, true);
    
    $rawresult->reset();
    while ($row = $rawresult->fetchArray(SQLITE3_ASSOC)) {
      foreach ($row as $strField => $varValue) {
        $custom->set($strField, $this->convertType($arrFields[$strField]['type'] ,$varValue));
      }
    }
	}
	
	/**
   * The class constructor
   * @param object $config[optional] The configuration object
   * @cfg string location The location of the datasource to use
   * @cfg string user The user to use for datasource authentication
   * @cfg string password The password to use for authentication
   * @cfg object log A log object to log errors to (must implement the interface IWPLog).
   * If no log object given, the errors are logged to an array which can be retrieved by getLog
   */
	public function __construct($config = null)
	{
		parent::__construct($config);
	}
	
	/** 
   * Permanently deletes data from a table
   * @param string $table The table to delete data of.
   * @param string $condition The condition to use for matching the data to remove
   * @return bool True if successfully deleted, false else
   */
	public function delete($table, $condition, $sort='', $dir='', $limit=0, $start=0)
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
		if ($this->fireEvent('preDelete', array($this, $table, $condition)) !== true) {
      return false;
    }
		
		//-- Multi request --
		if ($this->isMultiRequest() === true && strpos($this->strMrFlags, '[delete]') !== false) {
			$this->arrMr[] = array(
        'mrtype' => 'delete',
        'table' => $table,
        'condition' => $condition,
        'sort' => $sort,
        'dir' => $dir,
        'limit' => $limit,
        'start' => $start
      );
      return true;
		}
		
		$this->reset();
		
		$connection = new Sqlite3($this->Location);
		if (!$connection) {
			$this->writeToLog($loc, '', '', '', '20101', '', array($this->Location));
			return false;
		}
		
		$strSql = $this->getSqlQueryForDelete($table, $condition, $sort, $dir, $limit, $start);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForDelete'));
			return false;
		}
		
		$result = $connection->query($strSql);
		if ($result === false) {
			$this->writeToLog($loc, '', '', '', '20103', '', array($connection->lastErrorMsg(), $strSql));
			return false;
		}
		
		$this->Total = $connection->changes();
		$this->RawResult = $result;
		$this->lastConnection = $connection;
		$this->fireEvent('postDelete', array($this, $table, $condition, $this->Total));
		return true;
	}
	
	/**
   * Executes a sql statement. 
   * @param string $statement The statement to execute
   * @return mixed True if successfully executed, false else
   */
	public function exec($statement)
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
    $this->reset();
    
    $connection = new Sqlite3($this->Location);
    if (!$connection) {
      $this->writeToLog($loc, '', '', '', '20101', '', array($this->Location));
      return false;
    }
        
    
    $result = $connection->query($statement);
    if($result === false){
      $this->writeToLog($loc, '', '', '', '20103', '', array($connection->lastErrorMsg(), $strSql));
      return false;
    }
    
    $this->RawResult = $result;
    $this->lastConnection = $connection;
    return true;
	}
	
	/** 
   * Retrieves the id of the last inserted dataset (for auto-increment only). This method 
   * may not be supported by all WpConnect implementations
   * @return var The id of the last inserted dataset
   */
	public function getInsertId()
	{
		return $this->varInsertId;
	}
	
	/** 
   * Retrieves the total number of datasets (not the number, limited by LIMIT)
   * @return int The number of datasets returned or affected by a query
   */
	public function getTotal()
	{
		return -1;
	}
	
	/** 
   * Insert data to a table
   * @param string $table The table to insert data to.
   * @param array $insertion An associative array containing key/value pairs of the data to insert
   * @return bool True if successfully done, else false
   */
	public function insert($table, $insert)
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
		if ($this->fireEvent('preInsert', array($this, $table, $insert)) !== true) {
      return false;
    }
		
		//-- Multi request --
		if ($this->isMultiRequest() === true && strpos($this->strMrFlags, '[insert]') !== false) {
			$this->arrMr[] = array(
        'mrtype' => 'insert',
        'table' => $table,
        'insertion' => $insert
      );
      return true;
		}
		
		$this->reset();
		
		$connection = new Sqlite3($this->Location);
		if (!$connection) {
			$this->writeToLog($loc, '', '', '', '20101', '', array($this->Location));
			return false;
		}
		
		$strSql = $this->getSqlQueryForInsert($table, $insert);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForInsert'));
			return false;
		}
		
		$result = $connection->query($strSql);
		if($result === false){
			$this->writeToLog($loc, '', '', '', '20103', '', array($connection->lastErrorMsg(), $strSql));
			return false;
		}
		
		$this->Total = $connection->changes();
		$this->RawResult = $result;
		$this->varInsertId = $connection->lastInsertRowID();
		$this->lastConnection = $connection;
		$this->fireEvent('postInsert', array($this, $table, $insert));
		return true;
	}
	
	/**
   * Starts processing all requests collected before for a multi request
   * @return The number of requests processed or false on error
   * @see startMultiRequest()
   * @see stopMultiRequest()
   */
	public function processMultiRequest()
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
		if ($this->isMultiRequest() !== true) {
			return 0;
		}
		
		//--- Connect to database ---
		$connection = new Sqlite3($this->Location);
		if (!$connection) {
			$this->writeToLog($loc, '', '', '', '20101', '', array($this->Location));
			return false;
		}
		
		$arrRequests = $this->arrMr;
		$connection->query('begin transaction');
		//-- Prepare and validate queries --
		foreach ($arrRequests as $objRequest) {
			
			switch ($objRequest['mrtype']) {
				case 'update' : $strQuery = $this->getSqlQueryForUpdate($objRequest['table'], $objRequest['insertion'], $objRequest['condition']); break;
        case 'insert' : $strQuery = $this->getSqlQueryForInsert($objRequest['table'], $objRequest['insertion']); break;
        case 'delete' : $strQuery = $this->getSqlQueryForDelete($objRequest['table'], $objRequest['condition']); break;
				default: $strQuery = 'n/a';
			}
			
			if ($strQuery === false) {
				$this->writeToLog($loc, 'Anonymous', 'Error preparing multi request', 
					'The multi request could not be prepared. Error in the following request: Type:'.$objRequest->MrType.
					'Table: '.$objRequest->Table, '99999');
				return false;
			}
			
			if ($strQuery === 'n/a') {
				$this->writeToLog($loc, 'Anonymous', 'Error preparing multi request', 
					'The multi request could not be prepared. Multi request type "'.$objRequest->MrType.'" is not supported for multi requests', '99999');
				return false;
			}
			
      if (!$connection->query($strQuery)) {
        $this->writeToLog($strLocation, 'Anonymous', 'Error processing multi request', 
          'Sqlite returned an error on requesting: '.$strQuery, '99999');
        @pg_query('rollback');
        return false;
      }
		}
    if (!$connection->query('commit transaction')) {
      $this->writeToLog($strLocation, 'Anonymous', 'Error committing transaction', 
        'Sqlite returned an error on commit: '.$connection->lastErrorMsg(), '99999');
      return false;
    }
	 
		return count($arrRequests);
	}
	
	/**
	 * Reset the connector for a new query
	 */
	public function reset()
	{
		$this->_lastTable ='';
    $this->varInsertId = null;
		if ($this->RawResult) {
			unset($this->RawResult);
		}
		if ($this->lastConnection) {
			$this->lastConnection->close();
		}
		
		parent::reset();
	}
	
	/** 
   * Selects data from a table by using a condition to match
   * @param string $fields The fields to retrieve from table as comma separated string or * for all fields
   * @param string $table The table to select data from.
   * @param string $condition The condition to use for data selection
   * @param string $sort[optional] The column(s) to sort the data by (default "")
   * @param string $dir[optional] The sort direction (ASC or DESC) (default "ASC")
   * @param int $limit[optional] The number of datasets to return
   * @param int $start[optional] The start dataset of the datasets to return
   * @return bool True if successfully done, false else
   */
	public function select($fields, $table, $condition, $limit=0, $start=0, $sort='', $dir='ASC')
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
		$this->reset();
		
    if ($this->fireEvent('preSelect', array($this, $table, $fields, $condition)) !== true) {
      return false;
    }
    
		$connection = new Sqlite3($this->Location);
		if (!$connection) {
			$this->writeToLog($loc, '', '', '', '20101', '', array($this->Location));
			return false;
		}
				
		$strSql = $this->getSqlQueryForSelect($fields, $table, $condition, $sort, $dir, $limit, $start);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForSelect'));
			return false;
		}
		
		$result = $connection->query($strSql);
		if($result === false){
			$this->writeToLog($loc, '', '', '', '20103', '', array($connection->lastErrorMsg(), $strSql));
			return false;
		}
		$result->reset();
		
		$this->RawResult = $result;
		$this->lastConnection = $connection;
    $this->fireEvent('postSelect', array($this, $table, $fields, $condition));
		return true;
	}
	
	/** 
   * Updates data of a table
   * @param string $table The table to update data of.
   * @param array $insertion An associative array containing key/value pairs of the data to update
   * If a field is missing, its value will not be updated but kept in table
   * @param string $condition The condition to use to find the data to update
   * @return bool True if succesfully updated, false else
   */
	public function update($table, $update, $condition, $limit=0, $start=0, $sort='', $dir='ASC')
	{
		$loc = basename(__FILE__).'/'.__METHOD__;
 		
    if ($this->fireEvent('preUpdate', array($this, $table, $condition, $update)) !== true) {
      return false;
    }
    
		//-- Multi request --
		if ($this->isMultiRequest() === true && strpos($this->strMrFlags, '[update]') !== false) {
			$this->arrMr[] = array(
        'mrtype' => 'update',
        'table' => $table,
        'insertion' => $update,
        'condition' => $condition
      );
      return true;
		}
		
		$this->reset();
		
		$connection = new Sqlite3($this->Location);
		if (!$connection) {
			$this->writeToLog($loc, '', '', '', '20101', '', array($this->Location));
			return false;
		}
		
		$strSql = $this->getSqlQueryForUpdate($table, $update, $condition, $sort, $dir, $limit, $start);
		if($strSql === false){
			$this->writeToLog($loc, '', '', '', '90101', '', array('getSqlQueryForUpdate'));
			return false;
		}
		
		$result = $connection->query($strSql);
		if($result === false){
			$this->writeToLog($loc, '', '', '', '20103', '', array($connection->lastErrorMsg(), $strSql));
			return false;
		}
		
		$this->Total = $connection->changes();
		$this->RawResult = $result;
		$this->lastConnection = $connection;
    $this->fireEvent('postUpdate', array($this, $table, $condition, $update, $this->Total));
		return true;
	}
}
/* End of file WpConnectSqlite3.php */
/* Location: ./wildpack/dataaccess/wpconnect/WpConnectSqlite3.php */ 
